package com.sampleCodesJava.day6;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;

import com.mysql.jdbc.PreparedStatement;

public class ConnectionManager {

	final static String dbUrl = "jdbc:mysql://172.24.24.29:3306/world";
	final static String user = "root";
	final static String password = "password";
	final static String driver = "com.mysql.jdbc.Driver";

	public Connection getConnection(String url, String user, String password,
			String driver) {
		Connection conn = null;

		try {
			// Load the driver class
			// If you are using any driver database then load the right driver
			// here.
			Class.forName(driver);

			// Create the connection using the static getConnection method
			// change the connection string, according to your db, ip, username
			// and password
			conn = DriverManager.getConnection(url, user, password);

		} catch (SQLException ex) {
			ex.printStackTrace();
		} catch (ClassNotFoundException ex2) {
			ex2.printStackTrace();
		}

		return conn;

	}

	public static void retrieveName() {
		Connection conn = null;
		ConnectionManager connMan = new ConnectionManager();
		Statement stat = null;
		List<UserName> listOfNames = null;

		try {

			conn = connMan.getConnection(dbUrl, user, password, driver);
			String sqlString = "select * from actor";
			stat = conn.prepareStatement(sqlString);

			ResultSet rs = stat.executeQuery(sqlString);

			while (rs.next()) {
				System.out.println("Actor Names: " + rs.getString("first_name")
						+ " " + rs.getString("last_name"));
			}

		} catch (SQLException e) {

			e.printStackTrace();
		} finally {
			try {
				if (stat != null) {
					stat.close();
				}
				if (conn != null) {
					conn.close();
				}

			} catch (Exception ex) {

			}
		}

	}
	public static void insertCountry(String countryCode, String name, String continent, String region){
		Connection conn = null;
		java.sql.PreparedStatement stat = null;
		int insertedRows = 0;
		
		if(conn == null){
			ConnectionManager connMan = new ConnectionManager();
			conn = connMan.getConnection(dbUrl, user, password, driver);
		}
		
		try {
			StringBuilder sb = new StringBuilder();
			sb.append("INSERT INTO country (Code, Name, Continent, Region) VALUES (?,?,?,?)");
			
			stat = conn.prepareStatement(sb.toString());
			stat.setString(1, countryCode);
			stat.setString(2, name);
			stat.setString(3, continent);
			stat.setString(4, region);
			
			insertedRows = stat.executeUpdate();
			System.out.println(insertedRows + " rows inserted.");
			System.out.println(sb.toString());
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		
		
		
		
	}
	
	
	public static void main(String[] args) {
//		retrieveName();
		insertCountry("PH1", "Philippines", "Asia", "Asian Region");
	}
}
